package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.common.constant.IncomeExpenseType
import com.opennews.openplatform.familyexpenseapi.common.constant.PaymentType
import com.opennews.openplatform.familyexpenseapi.entity.Tables.*
import com.opennews.openplatform.myspringbootcore.extension.isNullOrBlankOrLiteralNull
import com.opennews.openplatform.myspringbootcore.util.DateHelper
import org.jooq.DSLContext
import org.jooq.Record1
import org.jooq.SelectConditionStep
import org.jooq.impl.DSL
import org.springframework.stereotype.Service
import java.math.BigDecimal
import java.text.DecimalFormat
import java.time.LocalDateTime

@Service
class IncomeExpenseAnalysisService(
    private val dslContext: DSLContext,
    private val bankService: BankService,
    private val incomeExpenseDetailTypeService: IncomeExpenseDetailTypeService,
) {
    /**
     * Queries income expense summary for one month, including total income, total expense, and balance.
     *
     * @param accountGroupId the ID of the user's account group
     * @param uniqueDates    a set of dates to query
     * @return a list of maps containing income and expense summaries per date
     */
    fun queryMonthlySummaryList(accountGroupId: String, uniqueDates: Set<LocalDateTime>): List<Map<String, Any>> {
        val summaryList = mutableListOf<Map<String, Any>>()

        for (date in uniqueDates) {
            val result = dslContext
                .select(
                    DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).`as`("amount"),
                    INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.`as`("paymentType"),
                    INCOME_EXPENSE_DETAIL.TYPE.`as`("type"),
                )
                .from(INCOME_EXPENSE_DETAIL)
                .where(
                    INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(date.year))
                        .and(DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(date.monthValue))
                )
                .groupBy(
                    INCOME_EXPENSE_DETAIL.PAYMENT_TYPE,
                    INCOME_EXPENSE_DETAIL.TYPE
                )
                .fetchMaps()

            val cashIncome = calculateSummary(result, PaymentType.CASH, IncomeExpenseType.INCOME)
            val cashExpense = calculateSummary(result, PaymentType.CASH, IncomeExpenseType.EXPENSE)

            val creditCardIncome =
                calculateSummary(result, PaymentType.CREDIT_CARD, IncomeExpenseType.INCOME)

            val creditCardExpense =
                calculateSummary(result, PaymentType.CREDIT_CARD, IncomeExpenseType.EXPENSE)

            // Create the summary map for the date
            summaryList.add(
                mapOf(
                    "incomeExpenseDate" to date,
                    "cashIncome" to cashIncome,
                    "cashExpense" to cashExpense,
                    "cashBalance" to cashIncome.add(cashExpense),
                    "creditCardIncome" to creditCardIncome,
                    "creditCardExpense" to creditCardExpense,
                    "creditCardBalance" to creditCardIncome.add(creditCardExpense)
                )
            )
        }

        return summaryList
    }

    /**
     * Queries income expense summary based on a single day. Includes total income, total expense and balance.
     *
     * @param accountGroupId: The string of the user's account group id.
     * @param dates:          The date list which stores each incomeExpenseDate might duplicate.
     * @return The list of map instances which contains keys below.
     * incomeExpenseDate: The date of income or expense happens. No duplicate date in this list.
     * totalIncome: Float of the total income in this day.
     * totalExpense: Float of the total expense in this day.
     * balance: Float of the balance in this day.
     */
    fun queryMonthlySummaryList(accountGroupId: String, dates: List<LocalDateTime>): List<Map<String, Any>> {
        // Get unique dates from the list, with each date converted to the first day of its month.
        val uniqueDates = dates
            .map { DateHelper.getFirstDayOfMonth(it) }
            .toSet()

        // Calls the overloaded method with the unique dates
        return queryMonthlySummaryList(accountGroupId, uniqueDates)
    }

    /**
     * Queries income expense summary based on a single day. Includes total income, total expense, and balance.
     *
     * @param accountGroupId: The string of the user's account group id.
     * @param uniqueDates:    Stores each incomeExpenseDate without duplicates.
     * @return The list of map instances which contains keys below.
     * incomeExpenseDate: The date of income or expense happens. No duplicate date in this list.
     * cashIncome: Float of the total cash income on this day.
     * cashExpense: Float of the total cash expense on this day.
     * cashBalance: Float of the total cash balance on this day.
     * creditCardIncome: Float of the total credit card income on this day.
     * creditCardExpense: Float of the total credit card expense on this day.
     * creditCardBalance: Float of the total credit card balance on this day.
     */
    fun queryDailySummaryList(accountGroupId: String, uniqueDates: Set<LocalDateTime>): List<Map<String, Any>> {
        val result = mutableListOf<Map<String, Any>>()

        if (uniqueDates.isNotEmpty()) {
            val rawData = dslContext
                .select(
                    INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.`as`("incomeExpenseDate"),
                    DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).`as`("amount"),
                    INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.`as`("paymentType"),
                    INCOME_EXPENSE_DETAIL.TYPE.`as`("type"),
                )
                .from(INCOME_EXPENSE_DETAIL)
                .where(
                    INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.`in`(uniqueDates))
                )
                .groupBy(
                    INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE,
                    INCOME_EXPENSE_DETAIL.PAYMENT_TYPE,
                    INCOME_EXPENSE_DETAIL.TYPE
                )
                .fetchMaps()

            for (uniqueDate in uniqueDates) {
                val dateItems = rawData.filter { it["incomeExpenseDate"] == uniqueDate }

                val cashIncome = calculateSummary(dateItems, PaymentType.CASH, IncomeExpenseType.INCOME)

                val cashExpense =
                    calculateSummary(dateItems, PaymentType.CASH, IncomeExpenseType.EXPENSE)

                val creditCardIncome =
                    calculateSummary(dateItems, PaymentType.CREDIT_CARD, IncomeExpenseType.INCOME)

                val creditCardExpense =
                    calculateSummary(dateItems, PaymentType.CREDIT_CARD, IncomeExpenseType.EXPENSE)

                result.add(
                    mapOf(
                        "incomeExpenseDate" to uniqueDate,
                        "cashIncome" to cashIncome,
                        "cashExpense" to cashExpense,
                        "cashBalance" to cashIncome.add(cashExpense),
                        "creditCardIncome" to creditCardIncome,
                        "creditCardExpense" to creditCardExpense,
                        "creditCardBalance" to creditCardIncome.add(creditCardExpense)
                    )
                )
            }
        }

        return result
    }

    /**
     * Queries income expense summary based on a single day. Includes total income, total expense, and balance.
     *
     * @param accountGroupId: The string of the user's account group id.
     * @param dates:          The date list which stores each incomeExpenseDate might duplicate.
     * @return The list of map instances which contains keys below.
     * incomeExpenseDate: The date of income or expense happening. No duplicate date in this list.
     * totalIncome: Float of the total income on this day.
     * totalExpense: Float of the total expense on this day.
     * balance: Float of the balance on this day.
     */
    fun queryDailySummaryList(accountGroupId: String, dates: List<LocalDateTime>): List<Map<String, Any>> {
        // Gets unique date from the list.
        val uniqueDates = dates.toSet()

        // Queries the summary based on these dates and returns the result.
        return queryDailySummaryList(accountGroupId, uniqueDates)
    }

    /**
     * Queries all distinct income/expense years.
     *
     * @param accountGroupId: The string of the user account group id.
     * @return The list of Integers of existing years.
     */
    fun queryExistingDistinctYears(accountGroupId: String): List<Int> {
        return dslContext.selectDistinct(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE))
            .from(INCOME_EXPENSE_DETAIL)
            .where(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
            .orderBy(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).desc())
            .fetchInto(Int::class.java)
    }

    /**
     * Queries summary data of the provided year.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param year:           The int of the year whose summary data will be retrieved.
     * @return The map which contains the return result of the querySummaryByYears method.
     */
    fun queryAnnualSummary(accountGroupId: String, year: Int): Map<String, Any>? {
        val result = queryAnnualSummaryByYears(accountGroupId, listOf(year))

        return if (result.isNotEmpty()) result.first() else null
    }

    /**
     * Queries the summary based on the type and year for each month.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param type:           The string of the income/expense type.
     * @param paymentType:    The string of the payment type.
     * @param year:           The Integer of year.
     * @return The map instance which contains keys below.
     * (year): The double of the total amount of this year.
     * (month): The double of the total amount of this month.
     */
    fun queryAnnualMonthlySummary(
        accountGroupId: String,
        type: String,
        paymentType: String,
        year: Int,
    ): Map<String, Any> {
        // LinkedHashMap preserves the insertion order of the keys.
        val result = LinkedHashMap<String, Any>()

        var totalAmount = BigDecimal.ZERO

        // Queries all existing monthly amount data in this year.
        val existingMonthlyAmountList = dslContext
            .select(
                DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).`as`("month"),
                DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).`as`("amount"),
            )
            .from(INCOME_EXPENSE_DETAIL)
            .join(INCOME_EXPENSE_DETAIL_TYPE).on(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(INCOME_EXPENSE_DETAIL_TYPE.NAME))
            .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID))
            .where(
                INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isTrue()
                    .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                    .and(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(year))
                    .and(INCOME_EXPENSE_DETAIL.TYPE.eq(type))
                    .and(INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.eq(paymentType))
            )
            .groupBy(DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE))
            .fetchMaps()

        val decimalFormat = DecimalFormat("00")

        // Prepares the amount of 12 months based on existing monthly amount data.
        for (i in 0 until 12) {
            val month = i + 1

            // The amount of each month.
            var amount = BigDecimal.ZERO

            // Gets the monthly amount data from query result for current month.
            val monthlyAmount = existingMonthlyAmountList.firstOrNull { (it["month"] as Int) == month }

            // If monthly data exists, then update amount.
            monthlyAmount?.let {
                amount = it["amount"] as BigDecimal
            }

            // Calculates the annual total amount.
            totalAmount = totalAmount.add(amount)

            // Formats the month to string. Example: 1 to 01.
            val monthString = decimalFormat.format(month)

            // Puts the amount into the map with month string as key.
            result[monthString] = amount
        }

        // Puts the total amount into the map with year string as key.
        result[year.toString()] = totalAmount

        return result
    }

    /**
     * Queries the summary based on the type and month for each day.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param type:           The string of the income/expense type.
     * @param paymentType:    The string of the payment type.
     * @param year:           The Integer of year.
     * @return The map instance which contains keys below.
     * (year): The double of the total amount of this year.
     * (month): The double of the total amount of this month.
     */
    fun queryMonthlyDailySummary(
        accountGroupId: String,
        type: String,
        paymentType: String,
        year: Int,
        month: Int,
    ): Map<String, Any> {
        // LinkedHashMap preserves the insertion order of the keys.
        val result = LinkedHashMap<String, Any>()

        var totalAmount = BigDecimal.ZERO

        // Queries all existing monthly amount data in this year.
        val existingDailyAmountList = dslContext.select(
            DSL.day(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).`as`("day"),
            DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).`as`("amount")
        )
            .from(INCOME_EXPENSE_DETAIL)
            .join(INCOME_EXPENSE_DETAIL_TYPE).on(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(INCOME_EXPENSE_DETAIL_TYPE.NAME))
            .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID))
            .where(
                INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isTrue()
                    .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                    .and(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(year))
                    .and(DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(month))
                    .and(INCOME_EXPENSE_DETAIL.TYPE.eq(type))
                    .and(INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.eq(paymentType))
            )
            .groupBy(DSL.day(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE))
            .fetchMaps()

        val lastDay = DateHelper.getLastDayOfMonth(year, month).dayOfMonth
        val decimalFormat = DecimalFormat("00")

        // Prepares the amount of days based on existing daily amount data.
        for (i in 0 until lastDay) {
            // Index is 0 based.
            val day = i + 1

            // The amount of each day.
            var amount = BigDecimal.ZERO

            // Gets the daily amount data from query result for current day.
            val dailyAmount = existingDailyAmountList.firstOrNull { (it["day"] as Int) == day }

            // If daily data exists, then update amount.
            dailyAmount?.let {
                amount = it["amount"] as BigDecimal
            }

            // Calculates the annual total amount.
            totalAmount = totalAmount.add(amount)

            // Formats the day to string. Example: 1 to 01.
            val dayString = decimalFormat.format(day)

            // Puts the amount into the map with day string as key.
            result[dayString] = amount
        }

        // Puts the total amount into the map with year-month string as key.
        result["$year$month"] = totalAmount

        return result
    }

    /**
     * Queries the top bank order by amount based on type and payment type in particular year and month.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param pageSize:       How many records are returned in one trip.
     * @param pageIndex:      Where to take rows.
     * @param type:           The string of income/expense type.
     * @param paymentType:    The string of payment type.
     * @param year:           The Integer of year.
     * @param month:          The Integer of month. It can be null.
     * @return The list of map instance which contains keys below.
     * id: String of the income expense detail id.
     * incomeExpenseDate: Date of the income expense detail.
     * amount: Float of the income expense detail amount.
     * type: String of the type. It's income or expense.
     * detailType: String of the detail type which indicates where the income from or the expense goes.
     * paymentType: String of the payment type which how the expense is paid.
     * bankId: String of the bank id of the bank if the expense is paid by card.
     * bankShortName: String of the short name of the bank if the expense is paid by card.
     * bankLogoUrl: String of the logo url of the bank if the expense is paid by card.
     * bankCardId: String of the id of the bank card if the expense is paid by card.
     * bankCardName: String of the name of the bank card if the expense is paid by card.
     * bankCardIsCreditCard: Boolean if this bank card is a credit card.
     * details: String of the income or expense detail.
     */
    fun queryTopRankByAccountGroup(
        accountGroupId: String,
        pageSize: Int,
        pageIndex: Int,
        type: String,
        paymentType: String?,
        year: Int?,
        month: Int?,
    ): List<Map<String, Any>> {
        return dslContext
            .select(
                INCOME_EXPENSE_DETAIL.ID.`as`("id"),
                INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.`as`("incomeExpenseDate"),
                INCOME_EXPENSE_DETAIL.AMOUNT.`as`("amount"),
                INCOME_EXPENSE_DETAIL.TYPE.`as`("type"),
                INCOME_EXPENSE_DETAIL.DETAIL_TYPE.`as`("detailType"),
                INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.`as`("paymentType"),
                BANK.ID.eq("bankId"),
                BANK.SHORT_NAME.`as`("bankShortName"),
                BANK_CARD.ID.`as`("bankCardId"),
                BANK_CARD.NAME.`as`("bankCardName"),
                BANK_CARD.IS_CREDIT_CARD.`as`("bankCardIsCreditCard"),
                INCOME_EXPENSE_DETAIL.DETAILS.`as`("details"),
            )
            .from(INCOME_EXPENSE_DETAIL)
            .join(INCOME_EXPENSE_DETAIL_TYPE).on(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(INCOME_EXPENSE_DETAIL_TYPE.NAME))
            .leftJoin(BANK_CARD).on(INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(BANK_CARD.ID))
            .leftJoin(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
            .where(
                INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isTrue())
                    .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                    .and(INCOME_EXPENSE_DETAIL.TYPE.eq(type))
                    .and(
                        if (paymentType.isNullOrBlankOrLiteralNull()) DSL.trueCondition()
                        else INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.eq(paymentType)
                    )
                    .and(
                        if (year == null) DSL.trueCondition()
                        else DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(year)
                    )
                    .and(
                        if (month == null) DSL.trueCondition()
                        else DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(month)
                    )
            )
            .orderBy(DSL.abs(INCOME_EXPENSE_DETAIL.AMOUNT).desc())
            .limit(pageSize)
            .offset(pageIndex * pageSize)
            .fetch()
            .map { item ->
                val result = item.intoMap()
                result["bankLogoUrl"] = bankService.getLogoUrl(item["bankShortName"] as String?)

                result
            }
    }

    /**
     * Queries the expense summary based on the type and year.
     * It focuses on expense detail type and does not include the credit card repayment.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param paymentType:    The string of payment type.
     * @param year:           The Integer of year.
     * @param month:          The Integer of month. This can be null.
     * @return The list of map instance which contains keys below.
     * detailType: The string of the detail type. The value is corresponding amount.
     * amount: The corresponding amount of this detail type.
     */
    fun queryExpenseByDetailType(
        accountGroupId: String,
        paymentType: String,
        year: Int?,
        month: Int?,
    ): List<Map<String, Any>> {
        // Queries the detail type names.
        val detailTypes = incomeExpenseDetailTypeService.queryNameForAnalysisByAccountGroup(
            accountGroupId,
            IncomeExpenseType.EXPENSE,
        )

        return querySummaryByDetailType(
            accountGroupId,
            paymentType,
            year,
            month,
            IncomeExpenseType.EXPENSE,
            detailTypes,
        )
    }

    /**
     * Queries the income summary based on the type and year.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param paymentType:    The string of payment type.
     * @param year:           The Integer of year.
     * @param month:          The Integer of month. This can be null.
     * @return The list of map instance which contains keys below.
     * detailType: The string of the detail type. The value is corresponding amount.
     * amount: The corresponding amount of this detail type.
     */
    fun queryIncomeByDetailType(
        accountGroupId: String,
        paymentType: String,
        year: Int?,
        month: Int?,
    ): List<Map<String, Any>> {
        // Queries the detail type names.
        val detailTypes = incomeExpenseDetailTypeService.queryNameForAnalysisByAccountGroup(
            accountGroupId,
            IncomeExpenseType.INCOME,
        )

        return querySummaryByDetailType(
            accountGroupId,
            paymentType,
            year,
            month,
            IncomeExpenseType.INCOME,
            detailTypes,
        )
    }

    /**
     * Queries the overall summary for account group.
     *
     * @param accountGroupId: The string of the user's account group id.
     * @return The map of maps containing keys below.
     * cashIncome: The total cash income amount of overall.
     * cashExpense: The total cash expense amount of overall.
     * cashBalance: The total cash balance amount of overall.
     * creditCardIncome: The total credit card income amount of overall.
     * creditCardExpense: The total credit card expense amount of overall.
     * creditCardBalance: The total credit card balance amount of overall.
     */
    fun queryOverallSummary(accountGroupId: String): Map<String, Map<String, Any>> {
        val result: MutableMap<String, MutableMap<String, Any>> = mutableMapOf(
            "cashIncomeSummary" to mutableMapOf(),
            "cashExpenseSummary" to mutableMapOf(),
            "creditCardIncomeSummary" to mutableMapOf(),
            "creditCardExpenseSummary" to mutableMapOf()
        )

        // Queries all SUM data for each year.
        // By now IncomeExpenseDetail and IncomeExpenseDetailType have no reference field. That's means no foreign key.
        // That's caused join operation takes 250+ms but sub-query takes 10+ms.
        val overallSummary = dslContext
            .select(
                DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).`as`("year"),
                INCOME_EXPENSE_DETAIL.TYPE.`as`("type"),
                INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.`as`("paymentType"),
                DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).`as`("amount"),
            )
            .from(INCOME_EXPENSE_DETAIL)
            .where(
                INCOME_EXPENSE_DETAIL.DETAIL_TYPE.`in`(buildAnalysisIncludedIncomeExpenseDetailTypeQuery(accountGroupId))
                    .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
            )
            .groupBy(
                DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE),
                INCOME_EXPENSE_DETAIL.TYPE,
                INCOME_EXPENSE_DETAIL.PAYMENT_TYPE
            )
            .fetchMaps()

        val distinctYears = overallSummary
            .map { it["year"].toString().toInt() }
            .distinct()

        var cashIncomeSummary: BigDecimal
        var totalCashIncomeSummary = BigDecimal.ZERO
        var cashExpenseSummary: BigDecimal
        var totalCashExpenseSummary = BigDecimal.ZERO
        var creditCardIncomeSummary: BigDecimal
        var totalCreditCardIncomeSummary = BigDecimal.ZERO
        var creditCardExpenseSummary: BigDecimal
        var totalCreditCardExpenseSummary = BigDecimal.ZERO

        for (year in distinctYears) {
            cashIncomeSummary =
                calculateSummary(overallSummary, year, PaymentType.CASH, IncomeExpenseType.INCOME)

            totalCashIncomeSummary = totalCashIncomeSummary.add(cashIncomeSummary)
            result["cashIncomeSummary"]?.put(year.toString(), cashIncomeSummary)

            cashExpenseSummary =
                calculateSummary(overallSummary, year, PaymentType.CASH, IncomeExpenseType.EXPENSE)

            totalCashExpenseSummary = totalCashExpenseSummary.add(cashExpenseSummary)
            result["cashExpenseSummary"]?.put(year.toString(), cashExpenseSummary)

            creditCardIncomeSummary = calculateSummary(
                overallSummary,
                year,
                PaymentType.CREDIT_CARD,
                IncomeExpenseType.INCOME,
            )

            totalCreditCardIncomeSummary = totalCreditCardIncomeSummary.add(creditCardIncomeSummary)
            result["creditCardIncomeSummary"]?.put(year.toString(), creditCardIncomeSummary)

            creditCardExpenseSummary = calculateSummary(
                overallSummary,
                year,
                PaymentType.CREDIT_CARD,
                IncomeExpenseType.EXPENSE,
            )

            totalCreditCardExpenseSummary = totalCreditCardExpenseSummary.add(creditCardExpenseSummary)
            result["creditCardExpenseSummary"]?.put(year.toString(), creditCardExpenseSummary)
        }

        result["cashIncomeSummary"]?.put("total", totalCashIncomeSummary)
        result["cashExpenseSummary"]?.put("total", totalCashExpenseSummary)
        result["creditCardIncomeSummary"]?.put("total", totalCreditCardIncomeSummary)
        result["creditCardExpenseSummary"]?.put("total", totalCreditCardExpenseSummary)

        return result
    }

    /**
     * Queries the annual summary in the specific years.
     *
     * @param accountGroupId The string of the user's account group id.
     * @param years The list of years to query.
     * @return A list of map instances containing:
     * - year: The year of the summary
     * - cashIncome: Total cash income
     * - cashExpense: Total cash expense
     * - cashBalance: Net balance from cash (income + expense)
     * - creditCardIncome: Total credit card income
     * - creditCardExpense: Total credit card expense
     * - creditCardBalance: Net balance from credit card (income + expense)
     */
    fun queryAnnualSummaryByYears(accountGroupId: String, years: List<Int>?): List<Map<String, Any>> {
        val result = mutableListOf<Map<String, Any>>()

        if (years?.isNotEmpty() == true) {
            // Queries all SUM data for each year.
            // IncomeExpenseDetail and IncomeExpenseDetailType have no foreign key reference.
            // Join takes 250+ms; sub-query takes 10+ms.
            val summaryList = dslContext
                .select(
                    DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).`as`("year"),
                    INCOME_EXPENSE_DETAIL.TYPE.`as`("type"),
                    INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.`as`("paymentType"),
                    DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).`as`("amount"),
                )
                .from(INCOME_EXPENSE_DETAIL)
                .where(
                    INCOME_EXPENSE_DETAIL.DETAIL_TYPE.`in`(
                        buildAnalysisIncludedIncomeExpenseDetailTypeQuery(
                            accountGroupId
                        )
                    )
                        .and(INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                        .and(DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).`in`(years))
                )
                .groupBy(
                    DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE),
                    INCOME_EXPENSE_DETAIL.TYPE,
                    INCOME_EXPENSE_DETAIL.PAYMENT_TYPE
                )
                .fetchMaps()

            var totalCashIncome = BigDecimal.ZERO
            var totalCashExpense = BigDecimal.ZERO
            var totalCreditCardIncome = BigDecimal.ZERO
            var totalCreditCardExpense = BigDecimal.ZERO

            for (year in years) {
                val cashIncome =
                    calculateSummary(summaryList, year, PaymentType.CASH, IncomeExpenseType.INCOME)

                val cashExpense =
                    calculateSummary(summaryList, year, PaymentType.CASH, IncomeExpenseType.EXPENSE)

                val creditCardIncome = calculateSummary(
                    summaryList,
                    year,
                    PaymentType.CREDIT_CARD,
                    IncomeExpenseType.INCOME,
                )

                val creditCardExpense = calculateSummary(
                    summaryList,
                    year,
                    PaymentType.CREDIT_CARD,
                    IncomeExpenseType.EXPENSE,
                )

                totalCashIncome += cashIncome
                totalCashExpense += cashExpense
                totalCreditCardIncome += creditCardIncome
                totalCreditCardExpense += creditCardExpense

                result.add(
                    mapOf(
                        "year" to year,
                        "cashIncome" to cashIncome,
                        "cashExpense" to cashExpense,
                        "cashBalance" to cashIncome + cashExpense,
                        "creditCardIncome" to creditCardIncome,
                        "creditCardExpense" to creditCardExpense,
                        "creditCardBalance" to creditCardIncome + creditCardExpense
                    )
                )
            }

            result.add(
                0, mapOf(
                    "year" to "${years.last()} - ${years.first()}",
                    "cashIncome" to totalCashIncome,
                    "cashExpense" to totalCashExpense,
                    "cashBalance" to totalCashIncome + totalCashExpense,
                    "creditCardIncome" to totalCreditCardIncome,
                    "creditCardExpense" to totalCreditCardExpense,
                    "creditCardBalance" to totalCreditCardIncome + totalCreditCardExpense
                )
            )
        }

        return result
    }

    private fun calculateSummary(
        result: List<Map<String, Any>>,
        year: Int?,
        paymentType: String,
        type: String,
    ): BigDecimal {
        return result
            .filter { item ->
                (year == null || item["year"].toString().toInt() == year) &&
                        item["paymentType"] == paymentType &&
                        item["type"] == type
            }
            .map { item -> item["amount"] as BigDecimal }
            .fold(BigDecimal.ZERO) { acc, amount -> acc + amount }
    }

    private fun calculateSummary(
        result: List<Map<String, Any>>,
        paymentType: String,
        type: String,
    ): BigDecimal = calculateSummary(result, null, paymentType, type)

    private fun buildAnalysisIncludedIncomeExpenseDetailTypeQuery(accountGroupId: String): SelectConditionStep<Record1<String>> {
        return dslContext
            .select(INCOME_EXPENSE_DETAIL_TYPE.NAME)
            .from(INCOME_EXPENSE_DETAIL_TYPE)
            .where(
                INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isTrue())
            )
    }

    /**
     * Queries the summary based on the year and month, type and detail types.
     *
     * @param accountGroupId: The string of the user's account group id.
     * @param paymentType:    The string of payment type.
     * @param year:           The Integer of year.
     * @param month:          The Integer of month.
     * @param type:           The string of IncomeExpenseTypeConstant.
     * @param detailTypes:    The list of ExpenseTypeConstant or IncomeTypeConstant.
     * @return The list of map instances which contain keys below:
     * detailType: The string of the detail type. The value is corresponding amount.
     * amount: The corresponding amount of this detail type.
     */
    private fun querySummaryByDetailType(
        accountGroupId: String,
        paymentType: String?,
        year: Int?,
        month: Int?,
        type: String,
        detailTypes: List<String>?,
    ): List<Map<String, Any>> {
        var result: List<Map<String, Any>> = emptyList()

        if (detailTypes?.isNotEmpty() == true) {
            result = dslContext
                .select(
                    DSL.coalesce(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT), BigDecimal.ZERO).`as`("amount"),
                    INCOME_EXPENSE_DETAIL.DETAIL_TYPE.`as`("detailType"),
                )
                .from(INCOME_EXPENSE_DETAIL)
                .where(
                    INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(INCOME_EXPENSE_DETAIL.TYPE.eq(type))
                        .and(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.`in`(detailTypes))
                        .and(
                            if (paymentType.isNullOrBlankOrLiteralNull()) DSL.trueCondition()
                            else INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.eq(paymentType)
                        )
                        .and(
                            if (year == null) DSL.trueCondition()
                            else DSL.year(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(year)
                        )
                        .and(
                            if (month == null) DSL.trueCondition()
                            else DSL.month(INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(month)
                        )
                )
                .groupBy(INCOME_EXPENSE_DETAIL.DETAIL_TYPE)
                .orderBy(DSL.abs(DSL.sum(INCOME_EXPENSE_DETAIL.AMOUNT)).desc())
                .fetchMaps()
        }

        return result
    }
}